﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;

namespace EARS.App_Code
{
    public class ParticipationDataClass
    {
        public void Register(string eventName, string userID, string name, int contactNo, string email)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EarsDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "insert into Participation(EventName, UserID, Name, ContactNo, Email) values(@EventName, @UserID, @Name, @ContactNo, @Email)";
                comm.Parameters.AddWithValue("@EventName", eventName);
                comm.Parameters.AddWithValue("@UserID", userID);
                comm.Parameters.AddWithValue("@Name", name);
                comm.Parameters.AddWithValue("@ContactNo", contactNo);
                comm.Parameters.AddWithValue("@Email", email);
                int rows = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }

        public void Unregister(string eventName, string userID)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EarsDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "delete from Participation where EventName = @EventName and UserID = @UserID";
                comm.Parameters.AddWithValue("@EventName", eventName);
                comm.Parameters.AddWithValue("@UserID", userID);
                int rows = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }

        public List<string> MyEvent(string userID)
        {
            List<string> myEvent = new List<string>();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EarsDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select EventName from Participation where UserID = @UserID";
                comm.Parameters.AddWithValue("@UserID", userID);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.HasRows)
                {
                    for (int i = 0; i < 5; i++)
                    {
                        if (dr.Read())
                        {
                            myEvent.Add(dr["EventName"].ToString());
                        }
                    }
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return myEvent;
        }

        public List<string> MyEvent(string userID, string dateCreated)
        {
            List<string> myEvent = new List<string>();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EarsDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select p.EventName from Participation p, Event e where p.UserID = @UserID and p.EventName in (select e.EventName from Event where e.DateCreated <= @DateCreated)";
                comm.Parameters.AddWithValue("@UserID", userID);
                comm.Parameters.AddWithValue("@DateCreated", dateCreated);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        myEvent.Add(dr["EventName"].ToString());
                    }
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return myEvent;
        }

        public int NumberOfParticipant(string eventName)
        {
            int num = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EarsDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select COUNT(EventName) from Participation where EventName = @EventName";
                comm.Parameters.AddWithValue("@EventName", eventName);
                num = (int)comm.ExecuteScalar();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return num;
        }

        public bool checkIfRegistered(string userID, string eventName)
        {
            bool check = false;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EarsDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select UserID, EventName from Participation where UserID = @UserID and EventName = @EventName";
                comm.Parameters.AddWithValue("@UserID", userID);
                comm.Parameters.AddWithValue("@EventName", eventName);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.HasRows)
                {
                    check = true;
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return check;
        }

        public void EventDeleted(string eventName)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EarsDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "delete from Participation where EventName = @EventName";
                comm.Parameters.AddWithValue("@EventName", eventName);
                int rows = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }
    }
}